package org.deng.vertx.common.util.excel;


import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.deng.vertx.common.util.DateUtils;
import org.deng.vertx.common.util.excel.model.*;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.NumberFormat;
import java.time.LocalDateTime;
import java.util.*;

/**
 * @author 邓力宾
 * @date 2020-08-06 14:07
 * @desc excel表格操作，包括读取和写入
 */
public class ExcelUtils {
    //新建数字格式对象
    private static  NumberFormat nf = NumberFormat.getInstance();
    static {
        //保留小数位4位
        nf.setMaximumFractionDigits(4);
        //是否保留千分位
        nf.setGroupingUsed(false);
    }

    private ExcelUtils(){}

    public static List<String> getXlsSheetNames(InputStream inputStream) throws IOException {
        List<String> list = new ArrayList<>(16);
        POIFSFileSystem poifsFileSystem = new POIFSFileSystem(inputStream);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(poifsFileSystem);
        int numberOfSheets = hssfWorkbook.getNumberOfSheets();
        for(int i = 0; i < numberOfSheets; i++){
            HSSFSheet sheet = hssfWorkbook.getSheetAt(i);
            list.add(sheet.getSheetName());
        }
        poifsFileSystem.close();
        inputStream.close();
        return list;
    }

    /**
     * 解析xls （excel 2003）
     * @param inputStream 输入流
     * @param cellNums 每个sheet有多少列
     * @return
     */
    public static ExcelModel parseXls(InputStream inputStream, int... cellNums){
        ExcelModel excelModel = new ExcelModel();
        List<SheetModel> sheetModels = new LinkedList<>();
        excelModel.setSheets(sheetModels);
        try {
            POIFSFileSystem poifsFileSystem = new POIFSFileSystem(inputStream);
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(poifsFileSystem);
            int numberOfSheets = hssfWorkbook.getNumberOfSheets();
            //遍历表
            for(int i = 0; i < numberOfSheets; i++){
                HSSFSheet sheet = hssfWorkbook.getSheetAt(i);
                if(sheet != null){
                    SheetModel sheetModel = new SheetModel();
                    sheetModels.add(sheetModel);
                    sheetModel.setSheetName(sheet.getSheetName());
                    sheetModel.setRows(new LinkedList<>());
                    int rowNum = sheet.getLastRowNum();
                    //遍历行
                    for(int j = 0; j <= rowNum; j++){
                        HSSFRow row = sheet.getRow(j);
                        if(row != null){
                            RowModel rowModel = new RowModel();
                            if(j == 0){
                                sheetModel.setHeaders(rowModel); //表头
                            }
                            boolean isAllCellEmpty = true;
                            //遍历列
                            for(int k = 0; k < cellNums[i]; k++ ) {
                                HSSFCell cell = row.getCell(k);
                                if (cell != null) {
                                    cell.setCellType(CellType.STRING); //设置成string类型
                                    String stringCellValue = cell.getStringCellValue();
                                    if( stringCellValue.length() > 0){
                                        isAllCellEmpty = false;
                                    }
                                    rowModel.add(stringCellValue); //将单元格数据添加到list
                                }else {
                                    rowModel.add("");
                                }
                            }
                            if(!isAllCellEmpty && j > 0){
                                sheetModel.getRows().add(rowModel);
                            }
                        }

                    }
                }
            }
            poifsFileSystem.close();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {

            if(inputStream != null){
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return excelModel;
    }

    /**
     * 解析xlsx格式 2007版本
     * @param inputStream 输入流
     * @param cellNums 每个sheet有多少列
     */
    public static ExcelModel parseXlsx(InputStream inputStream, int... cellNums){
        ExcelModel excelModel = new ExcelModel();
        List<SheetModel> sheetModels = new LinkedList<>();
        excelModel.setSheets(sheetModels);
        try {
            //读取的时候可以使用流，也可以直接使用文件名
            XSSFWorkbook xwb = new XSSFWorkbook(inputStream);
            //循环工作表sheet
            for (int numSheet = 0; numSheet < xwb.getNumberOfSheets(); numSheet++) {
                XSSFSheet xSheet = xwb.getSheetAt(numSheet);
                if (xSheet == null) {
                    continue;
                }
                SheetModel sheetModel = new SheetModel();
                sheetModels.add(sheetModel);
                sheetModel.setSheetName(xSheet.getSheetName());
                List<RowModel> rowModels = new LinkedList<>();
                sheetModel.setRows(rowModels);
                //循环行row
                for (int numRow = 0; numRow <= xSheet.getLastRowNum(); numRow++) {
                    XSSFRow xRow = xSheet.getRow(numRow);
                    if (xRow == null) {
                        continue;
                    }
                    RowModel rowModel = new RowModel();
                    if(numRow == 0){
                        sheetModel.setHeaders(rowModel); //表头
                    }
                    boolean isAllCellEmpty = true;
                    //循环列cell
                    for (int numCell = 0; numCell < cellNums[numSheet]; numCell++) {
                        XSSFCell xCell = xRow.getCell(numCell);
                        //输出值
                        String value = getValue(xCell);
                        if(value != null && value.length() > 0){
                            isAllCellEmpty = false;
                        }
                        rowModel.add(value);
                    }
                    if(!isAllCellEmpty && numRow > 0){
                        sheetModel.getRows().add(rowModel);
                    }
                }

            }
            xwb.close();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            if(inputStream != null){
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return excelModel;
    }
    /**
     * 取出每列的值
     *
     * @param xCell 列
     * @return
     */
    private static  String getValue(XSSFCell xCell) {
        if(xCell == null){
            return "";
        }
        if (xCell.getCellType() == CellType.BOOLEAN) {
            return String.valueOf(xCell.getBooleanCellValue());
        } else if (xCell.getCellType() == CellType.NUMERIC) {
            double numericCellValue = xCell.getNumericCellValue();
            return nf.format(numericCellValue);
        } else {
            return String.valueOf(xCell.getStringCellValue());
        }
    }

    /**
     * 导出
     * @param outputStream 输出流
     * @param excelExportModel 数据和模型
     */
    public static void exportXls(OutputStream outputStream, ExcelExportModel excelExportModel) throws IOException,
            NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        //创建工作薄对象
        HSSFWorkbook workbook=new HSSFWorkbook();
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setBold(true);//粗体显示
        cellStyle.setFont(font);
        for (SheetExportModel sheetModel : excelExportModel.getSheets()) {
            //创建工作表对象
            HSSFSheet sheet = workbook.createSheet(sheetModel.getSheetName());
            sheet.setColumnWidth(0, 256 * 15);
            List<ColumnExportModel> columnExportModelList = sheetModel.getColumnExportModelList();
            // 创建表头
            int rowNum = 0;
            HSSFRow row = sheet.createRow(rowNum++);//设置第一行，从零开始
            for (int i = 0; i < columnExportModelList.size(); i++) {
                ColumnExportModel columnExportModel = columnExportModelList.get(i);
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(columnExportModel.getHeaderName());
            }
            //填充内容
            List<? extends Object> dataList = sheetModel.getDataList();
            if(dataList != null && !dataList.isEmpty()){
                for (Object o : dataList) {
                    // 创建一行
                    row = sheet.createRow(rowNum++);
                    // 当前行 内容
                    for (int i = 0; i < columnExportModelList.size(); i++) {
                        ColumnExportModel columnExportModel = columnExportModelList.get(i);
                        HSSFCell cell = row.createCell(i);
                        String propertyName = columnExportModel.getPropertyName();
                        String firstChar = propertyName.substring(0, 1).toUpperCase();

                        Method getMethod = o.getClass().getMethod("get" + firstChar + propertyName.substring(1));
                        Object result = getMethod.invoke(o);
                        cell.setCellValue(formatToString(result));
                    }
                }
            }
        }
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();

    }

    /**
     * 导出只有单个sheet的excel
     * @param outputStream 输出流
     * @param dataList 数据集合
     * @param columnHeaders 字段和表头 如: id|id,name|姓名,date|日期
     */
    public static void exportSingleSheet(OutputStream outputStream, List<? extends Object> dataList, String columnHeaders) throws
            InvocationTargetException, NoSuchMethodException, IllegalAccessException, IOException {

        ExcelExportModel excelExportModel = new ExcelExportModel();
        excelExportModel.setSheets(new ArrayList<>());
        excelExportModel.getSheets().add(new SheetExportModel());
        SheetExportModel sheetExportModel = excelExportModel.getSheets().get(0);
        sheetExportModel.setSheetName("sheet1");
        sheetExportModel.setDataList(dataList);
        sheetExportModel.setColumnExportModelList(new ArrayList<>());
        List<ColumnExportModel> columnExportModelList = sheetExportModel.getColumnExportModelList();
        String[] columnHeaderArr = columnHeaders.split(",");
        for (String columnHeader : columnHeaderArr) {
            String[] strings = columnHeader.split("\\|");
            columnExportModelList.add(new ColumnExportModel(strings[0].trim(),strings[1].trim()));
        }
        exportXls(outputStream, excelExportModel);

    }
    private static String formatToString(Object o){
        if(o == null){
            return "";
        }
        if(o instanceof Date){
            return DateUtils.format((Date) o, DateUtils.DATE_TIME_PATTERN);
        }

        if(o instanceof Double){
            return nf.format((Double) o);
        }
        if(o instanceof LocalDateTime){
            return DateUtils.formatDate((LocalDateTime) o);
        }
        if (o instanceof Boolean) {
            if (o.equals(true)){
                return "是";
            }
            return "否";
        }
        return String.valueOf(o);
    }

    /**
     * 根据模版填充数据 然后 导出
     * @param outputStream 输出流
     * @param xlsModelInputStream xls 表格模版输入流
     * @param sheetDataMap sheet名称-sheet数据
     * @param
     */
    public static void exportXlsByModel(OutputStream outputStream,
                                        InputStream xlsModelInputStream,
                                        Map<String, SheetData> sheetDataMap) throws IOException {
        //创建工作薄对象
        POIFSFileSystem poifsFileSystem = new POIFSFileSystem(xlsModelInputStream);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(poifsFileSystem);
        int numberOfSheets = hssfWorkbook.getNumberOfSheets();

        //遍历表
        for(int i = 0; i < numberOfSheets; i++) {
            HSSFSheet sheet = hssfWorkbook.getSheetAt(i);
            if (sheet != null) {
                String sheetName = sheet.getSheetName();
                SheetData excelDataMode2 = sheetDataMap.get(sheetName); //获取该sheet对应的数据
                if(excelDataMode2 != null){
                    int startRow = excelDataMode2.getStartRow(); // 从第几行开始
                    List<List<String>> dataList = excelDataMode2.getDataList(); // 数据
                    if(dataList != null){
                        for (List<String> rowList : dataList) { //所有的行
                            // 创建一行
                            HSSFRow row = sheet.createRow(startRow++);
                            // 当前行 内容
                            for (int j = 0; j < rowList.size(); j++) {

                                HSSFCell cell = row.createCell(j); //创建单元格
                                cell.setCellValue(rowList.get(j)); //填充数据
                            }
                        }
                    }
                }
            }
        }
        hssfWorkbook.write(outputStream);
        outputStream.close();
        xlsModelInputStream.close();
    }


}
